-- @owner: @qumin20145
-- @date: 2022-06-22
-- @testpoint: 子查询的目标列使用序列

--step1:建表;expect:成功
drop table if exists t_ustore_query_0105_1 cascade ;
drop table if exists t_ustore_query_0105_2 cascade ;
create table t_ustore_query_0105_1(id int not null,c_int int,c_vchar varchar(55) not null,c_vchar2 varchar(55) not null,c_blob blob not null,c_date date)
with (storage_type=ustore) partition by range(id)
(
partition p1 values less than(100),
partition p2 values less than(200),
partition p3 values less than(300)
);
create table t_ustore_query_0105_2(id int not null,c_int int,c_vchar varchar(55) not null,c_vchar2 varchar(55) not null,c_blob blob not null,c_date date)
 with (storage_type=ustore);

--step2:插入数据;expect:成功
drop procedure if exists proc_ustore_query_0105;
insert into t_ustore_query_0105_1 values(1,100,'abc123',lpad('123abc',50,'abc'),lpad('11100011',50,'1100')::blob,to_timestamp(to_char('1800-01-01 10:51:47'),'yyyy-mm-dd hh24:mi:ss'));
insert into t_ustore_query_0105_2 values(1,100,'abc123',lpad('123abc',50,'abc'),lpad('11100011',50,'1100')::blob,to_timestamp(to_char('1800-01-01 10:51:47'),'yyyy-mm-dd hh24:mi:ss'));
create or replace procedure proc_ustore_query_0105(tname varchar,startall int,endall int) as
sqlst varchar(500);
begin
  for i in startall..endall loop
        sqlst := 'insert into ' || tname ||' select id+'||i||',c_int+'||i||',c_vchar||'||i||',c_vchar2||'||i||',c_blob'||',c_date from '||tname|| ' where id=1';
        execute immediate sqlst;
  end loop;
end;
/
call proc_ustore_query_0105('t_ustore_query_0105_1',1,50);
call proc_ustore_query_0105('t_ustore_query_0105_1',10,30);
call proc_ustore_query_0105('t_ustore_query_0105_2',1,40);

--step3:创建序列;expect:成功
drop sequence if exists sqe_ustore_query_0105;
create sequence sqe_ustore_query_0105 start with 10 maxvalue 1000 increment by 2 cycle;

--step4:子查询的目标列使用序列;expect:成功
select t0.id from (select sqe_ustore_query_0105.nextval new_id,id from t_ustore_query_0105_1 where id <20 ) t0 inner join t_ustore_query_0105_2 t on t.id=t0.id order by t0.id limit 5;

--step5:清理环境;expect:成功
drop table t_ustore_query_0105_1 cascade;
drop table t_ustore_query_0105_2 cascade;
drop sequence sqe_ustore_query_0105;
drop procedure proc_ustore_query_0105;
